package com.legendshop.code.db.table;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.legendshop.code.config.Table;
import com.legendshop.code.db.Column;
import com.legendshop.code.db.DbType;

public class MysqlSchemaHelper implements DatabaseSchemaHelper {

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate(){
        if(jdbcTemplate == null){
            jdbcTemplate = new JdbcTemplate(DbType.getDataSource());
        }
        return jdbcTemplate;
    }
	@Override
	public Table getTableSimpleDefinition(String database, String tableName) {
		String sql = "select table_name, table_comment from information_schema.tables where  table_name = ? and table_schema = ?";
		try {
			List<?> rows = getJdbcTemplate().queryForList(sql, new Object[] { tableName, database });

			Table result = new Table();
			if (rows.size() > 0) {
				Map<?, ?> map = (Map<?, ?>) rows.get(0);
				result.setTableName(tableName);
				result.setTableComment((String) (map.get("TABLE_COMMENT")));
			} else {
				throw new RuntimeException("Can not find table: " + tableName);
			}

			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Column> getTableColumns(String database, String tableName) {
		String sql = "select DATA_TYPE, NUMERIC_PRECISION, COLUMN_NAME,COLUMN_COMMENT,COLUMN_DEFAULT, IS_NULLABLE, COLUMN_KEY from information_schema.columns where table_name = ? and table_schema = ?";
		try {
			List<?> rows = getJdbcTemplate().queryForList(sql, new Object[] { tableName, database });
			List<Column> list = new ArrayList<Column>();
			for (int i = 0; i < rows.size(); i++) {
				Map<?, ?> map = (Map<?, ?>) rows.get(i);
				Column column = new Column();
				column.setColumnComment((String) (map.get("COLUMN_COMMENT")));
				column.setColumnName((String) (map.get("COLUMN_NAME")));
				column.setDataType((String) (map.get("DATA_TYPE")));
				BigInteger numericPrecision= (BigInteger)(map.get("NUMERIC_PRECISION"));
				if(numericPrecision != null)
					column.setNumericPrecision(numericPrecision.longValue());
				column.setDefaultValue((map.get("COLUMN_DEFAULT")));

				String nullableStr = (String) (map.get("IS_NULLABLE"));
				if ("YES".equalsIgnoreCase(nullableStr)) {
					column.setNullable(true);
				} else {
					column.setNullable(false);
				}

				String isPrimaryKeyColumnString = (String) (map.get("COLUMN_KEY"));
				if ("PRI".equalsIgnoreCase(isPrimaryKeyColumnString)) {
					column.setPrimaryKeyColumn(true);
				} else {
					column.setPrimaryKeyColumn(false);
				}

				list.add(column);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<String> getAllTableName() {
		String sql = "select * from sys.all_tables order by owner ,table_name";
		return getJdbcTemplate().queryForList(sql, String.class);
	}

}
